package mssql

import (
	"context"
	"database/sql"
	"fmt"
	godbv2 "gitee.com/kinwyb/godb/v2"
	_ "github.com/denisenkom/go-mssqldb"
	"github.com/golang-sql/sqlexp"
	"github.com/spf13/cast"
	"net/url"
	"regexp"
	"strings"
	"sync"
	"time"
)

const DriverName godbv2.DriverName = "mssql"

// EmptyUniqueidentifier 空的uniqueidentifier值
const EmptyUniqueidentifier = "00000000-0000-0000-0000-000000000000"

// UniqueidentifierValue 创建一个uniqueidentifier值，当可能是null的时候用
func UniqueidentifierValue(val string) sql.NullString {
	ret := sql.NullString{String: val}
	ret.Valid = ret.String != ""
	return ret
}

// MSSQL 操作对象
type MSSQL struct {
	godbv2.Connect
	mssqlQuery
	linkString string
}

func (m *MSSQL) Transaction(t godbv2.TransactionFunc, option ...*sql.TxOptions) error {
	mquery := &mssqlQuery{}
	mtx := godbv2.NewTxConnectQuery(mquery)
	return m.NewTransaction(t, mtx.GetTxSQL, option...)
}

func (m *MSSQL) Close() {
	m.Connect.Close()
	m.mssqlQuery.Conn = nil
}

// Connect 链接mssql数据库
//
//eg:sqlserver://sa:mypass@localhost?database=master
func Connect(host, username, password, db string, params ...url.Values) (*MSSQL, error) {
	query := url.Values{}
	if len(params) > 0 {
		query = params[0]
	}
	query.Set("database", db)
	u := &url.URL{
		Scheme: "sqlserver",
		User:   url.UserPassword(username, password),
		Host:   fmt.Sprintf("%s", host),
		// Path:  instance, // if connecting to an instance instead of a port
		RawQuery: query.Encode(),
	}
	result := &MSSQL{
		linkString: u.String(),
	}
	sqlDB, err := sql.Open("sqlserver", result.linkString)
	if err != nil {
		return nil, err
	}
	sqlDB.SetMaxOpenConns(30)                  //最大连接数
	sqlDB.SetMaxIdleConns(25)                  //最大的空闲连接数25个
	sqlDB.SetConnMaxIdleTime(10 * time.Minute) //空闲10分钟后释放连接
	result.SetSQLDB(sqlDB)
	result.SetDataBaseName(db) //记录数据库名称,表名格式化会用到
	result.Conn = sqlDB
	result.DriverName = DriverName
	return result, nil
}

type mssqlQuery struct {
	godbv2.ConnectQuery
}

func (m *mssqlQuery) SetConn(conn godbv2.QueryWithContext) {
	m.ConnectQuery.Conn = conn
	m.ConnectQuery.CheckConn = false
}

func (m *mssqlQuery) QueryRows(sql string, args ...any) *godbv2.QueryResult {
	i := 0
	sql = regexp.MustCompile("(\\?)").ReplaceAllStringFunc(sql, func(s string) string {
		i++
		return fmt.Sprintf("@p%d", i)
	})
	if len(args) < i {
		return godbv2.ErrQueryResult(fmt.Errorf("参数缺少,目标参数%d个,实际参数%d个", i, len(args)), m.Dbname, sql, args)
	}
	return m.ConnectQuery.QueryRows(sql, args...)
}

func (m *mssqlQuery) QueryRow(sql string, args ...any) *godbv2.QueryResult {
	if ok, _ := regexp.MatchString("(?i)(.*?) TOP (.*?)\\s+(.*)?", sql); ok {
		sql = regexp.MustCompile("(?i)(.*?) TOP (.*?)\\s+(.*)?").ReplaceAllString(sql, "SELECT TOP 1 $3")
	} else {
		sql = strings.Replace(sql, "SELECT ", "SELECT TOP 1 ", 1)
	}
	return m.QueryRows(sql, args...)
}

func (m *mssqlQuery) QueryWithPage(sql string, page *godbv2.PageObj, args ...interface{}) *godbv2.QueryResult {
	if page == nil {
		return m.QueryRows(sql, args...)
	}
	regx := regexp.MustCompile("\\s+(\\[?[a-zA-Z0-9]+\\]+\\.)?\\[?[a-zA-Z0-9]+\\]?\\.[a-zA-Z]*\\.+")
	tableRepMap := map[string]string{}
	if regx.MatchString(sql) {
		sql = regx.ReplaceAllStringFunc(sql, func(s string) string {
			news := strings.ReplaceAll(s, "[", "@")
			news = strings.ReplaceAll(news, "]", "@")
			news = strings.ReplaceAll(news, ".", "@")
			tableRepMap[strings.TrimSpace(news)] = strings.TrimSpace(s)
			return news
		})
	}
	sqlInfo, err := godbv2.SelectSQLParser(sql)
	if err != nil {
		return godbv2.ErrQueryResult(err, m.Dbname, sql, args)
	}
	sqlInfo.Table = strings.ReplaceAll(sqlInfo.Table, "`", "")
	if len(tableRepMap) > 0 {
		for k, v := range tableRepMap {
			sqlInfo.Table = strings.ReplaceAll(sqlInfo.Table, k, v)
			sqlInfo.Table = strings.ReplaceAll(sqlInfo.Where, k, v)
		}
	}
	sqlBuilder := strings.Builder{}
	if page.Total < 1 {
		sqlBuilder.WriteString("SELECT count(0) num FROM ")
		sqlBuilder.WriteString(sqlInfo.Table)
		sqlBuilder.WriteString(sqlInfo.Where)
		sqlBuilder.WriteString(sqlInfo.GroupBy)
		sqlBuilder.WriteString(sqlInfo.Having)
		result := m.QueryRows(sqlBuilder.String(), args...)
		e := result.Error()
		if e != nil {
			return result
		}
		count := cast.ToInt64(cast.ToString(result.Get("num")))
		page.SetTotal(count)
		sqlBuilder.Reset()
	}
	currentpage := 0
	if page.Page-1 > 0 {
		currentpage = page.Page - 1
	}
	if page.Total < 1 {
		return godbv2.NewRowsResult(nil, sql, args)
	}
	sqlBuilder.WriteString("SELECT TOP ")
	sqlBuilder.WriteString(cast.ToString(page.Rows))
	sqlBuilder.WriteString(" * FROM (SELECT ROW_NUMBER() OVER (")
	sqlBuilder.WriteString(sqlInfo.OrderBy)
	sqlBuilder.WriteString(") as RowNumber,")
	sqlBuilder.WriteString(sqlInfo.Columns)
	sqlBuilder.WriteString(" FROM ")
	sqlBuilder.WriteString(sqlInfo.Table)
	sqlBuilder.WriteString(sqlInfo.Where)
	sqlBuilder.WriteString(sqlInfo.GroupBy)
	sqlBuilder.WriteString(sqlInfo.Having)
	sqlBuilder.WriteString(") as tmp WHERE RowNumber > ")
	sqlBuilder.WriteString(cast.ToString(page.Rows * currentpage))
	sqlBuilder.WriteString(" ORDER BY RowNumber ASC ")
	sql = sqlBuilder.String()
	return m.QueryRows(sql, args...)
}

func (m *mssqlQuery) Exec(sql string, args ...any) *godbv2.ExecResult {
	i := 0
	sql = regexp.MustCompile("(\\?)").ReplaceAllStringFunc(sql, func(s string) string {
		i++
		return fmt.Sprintf("@p%d", i)
	})
	if len(args) < i {
		return godbv2.ErrExecResult(fmt.Errorf("参数缺少,目标参数%d个,实际参数%d个", i, len(args)), m.Dbname, sql, args)
	}
	return m.ConnectQuery.Exec(sql, args...)
}

func (m *mssqlQuery) Prepare(query string) (*sql.Stmt, error) {
	i := 0
	query = regexp.MustCompile("(\\?)").ReplaceAllStringFunc(query, func(s string) string {
		i++
		return fmt.Sprintf("@p%d", i)
	})
	return m.ConnectQuery.Prepare(query)
}

// SQLPrinter sql服务输出的消息,MSSQL支持
// 使用方法:
//
//		printer := &SQLPrinter{}
//		res := conn.QueryRows(sql,args1,arg2,printer.ReturnMessage())
//	 	printer.Parse()
//		fmt.Printf("Print: %v\n",printer.Message)
//		fmt.Printf("Error: %s\n",printer.ErrMessage)
//		fmt.Printf("Rows: %d\n,res.Length())
type SQLPrinter struct {
	argIndex     int      `description:"参数位置"`
	Message      []string `description:"输出的内容"`
	ErrMessage   string   `description:"输出的错误"`
	queryMessage *sqlexp.ReturnMessage
	lock         sync.Mutex
}

func (s *SQLPrinter) ReturnMessage() *sqlexp.ReturnMessage {
	s.queryMessage = &sqlexp.ReturnMessage{}
	return s.queryMessage
}

// Parse 解析消息
func (s *SQLPrinter) Parse() {
	s.lock.Lock()
	defer s.lock.Unlock()
	if s.queryMessage != nil {
		ctx := context.Background()
		active := true
		for active {
			msg := s.queryMessage.Message(ctx)
			select {
			case <-ctx.Done():
				break
			default:
				switch m := msg.(type) {
				case sqlexp.MsgNotice:
					s.Message = append(s.Message, m.Message.String())
				case sqlexp.MsgError:
					s.ErrMessage = m.Error.Error()
				case sqlexp.MsgNextResultSet:
					active = false
				}
			}
		}
	}
	s.queryMessage = nil
}
